package com.dldata.devtools30.utils.dbsql;


import com.dldata.devtools30.utils.DateUtil;
import oracle.sql.DATE;
import oracle.sql.TIMESTAMP;
import oracle.sql.TIMESTAMPTZ;

import java.sql.*;
import java.text.MessageFormat;
import java.util.LinkedList;
import java.util.List;

/**
 * 扩展Table
 * 
 * @author askyear
 * 
 */
public class DlTablex {

	private static final long serialVersionUID = 1892825594017686875L;

	/**
	 * 表名
	 */
	private String tableName;

	/**
	 * 放字段
	 */
	protected String[] field = null;

	private List<Object> values =new LinkedList<>();

	public Object getPropertyValue(int index) {
		return values.get(index);
	}

	public void setPropertyValue(int index, Object value) {
		this.values.set(index, value);
	}


	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String[] getField() {
		return field;
	}

	public void setField(String[] field) {
		this.field = field;
		for (int i = 0; i < field.length; i++) {
			values.add(null);
		}
	}

	public int indexOfField(String fieldName) {
//		this.field = field;
		for (int i = 0; i < field.length; i++) {
			if(fieldName.equals(field[i])){
				return i;
			}
		}
		return -1;
	}


	/**
	 * buildInsertSql
	 *
	 * @param bean
	 * @return
	 */
	public static String buildInsertSql(DlTablex bean) {
		StringBuffer sb = new StringBuffer();
		String tableName = bean.getTableName();
		String[] fields = bean.getField();
		StringBuffer fieldsb = new StringBuffer();
		fieldsb.append(fields[0]);
		for (int i = 1; i < fields.length; i++) {
			fieldsb.append(",").append(fields[i]);
		}
//		sb.append("-- insert for " + tableName);
		sb.append("insert into ").append(tableName).append(" (").append(fieldsb).append(")");
		Object value = bean.getPropertyValue(0);
		sb.append(" values('").append(value == null ? "" : value.toString()).append("'");
		for (int j = 1; j < fields.length; j++) {
			value = bean.getPropertyValue(j);
			if(value == null){
				sb.append(",").append("NULL");
			} else if (value instanceof Boolean) {
//				if ((Boolean) value)
//					value = "1";
//				else
//					value = "0";
				sb.append(",'").append(value == null ? "false" : value.toString()).append("'");
			} else if (value instanceof Date) {
				Date clob= (Date)value;
				sb.append(",'").append(value == null ? "NULL" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(clob.getTime()))).append("'");
			} else if (value instanceof Time) {
				Time clob= (Time)value;
				sb.append(",'").append(value == null ? "NULL" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(clob.getTime()))).append("'");

			} else if (value instanceof TIMESTAMP) {
				TIMESTAMP clob= (TIMESTAMP)value;
				try {
					sb.append(",'").append(value == null ? "NULL" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(clob.dateValue().getTime()))).append("'");
				} catch (SQLException e) {
					e.printStackTrace();
					sb.append(",").append(value == null ? "NULL" : value.toString());
				}

			} else if (value instanceof DATE) {
				DATE clob= (DATE)value;
				sb.append(",'").append(value == null ? "NULL" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(clob.dateValue().getTime()))).append("'");

			} else if (value instanceof TIMESTAMPTZ) {
				TIMESTAMPTZ clob= (TIMESTAMPTZ)value;
				try {
					sb.append(",'").append(value == null ? "NULL" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(clob.dateValue().getTime()))).append("'");
				} catch (SQLException e) {
					e.printStackTrace();
					sb.append(",").append(value == null ? "NULL" : value.toString());
				}

			} else if (value instanceof Clob) {
				Clob clob = (Clob) value;;
				try {
					sb.append(",'").append(value == null ? "NULL" :clob.getSubString(1, (int) clob.length())).append("'");
				} catch (Exception e) {
					e.printStackTrace();
				}
			} else if (value instanceof Blob) {
				Blob blob= (Blob)value;
				sb.append(",").append(value == null ? "NULL" : value.toString());
			} else if (value instanceof Number) {
				sb.append(",").append(value == null ? "NULL" : value.toString());
			} else {
				sb.append(",'").append(value == null ? "''" : value.toString().replaceAll("\\r","\\\\r").replaceAll("\\n","\\\\n").replaceAll("'","\\\\'")).append("'");
			}
		}
		sb.append(");");
		return sb.toString();
	}

	/**
	 * buildInsertSql
	 *
	 * @param bean
	 * @return
	 */
	public static String buildUpdateSql(DlTablex bean) {

		String[] fields = bean.getField();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbId = new StringBuffer();
		for (int j = 0; j < fields.length; j++) {
			Object value = bean.getPropertyValue(j);

			if(fields[j].equalsIgnoreCase("id")){
				sbId.append(" ");
				sbId.append(fields[j]);
				sbId.append(" = '");
				sbId.append(value.toString());
				sbId.append("' ");
			}

			sb.append(" ");
			sb.append(fields[j]);
			sb.append(" = ");
			if(value == null){
				sb.append("NULL");
			} else if (value instanceof Boolean) {
				sb.append("'").append(value == null ? "false" : value.toString()).append("'");
			} else if (value instanceof Date) {
				Date clob= (Date)value;
				sb.append("'").append(value == null ? "NULL" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(clob.getTime()))).append("'");
			} else if (value instanceof Time) {
				Time clob= (Time)value;
				sb.append("'").append(value == null ? "NULL" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(clob.getTime()))).append("'");

			} else if (value instanceof TIMESTAMP) {
				TIMESTAMP clob= (TIMESTAMP)value;
				try {
					sb.append("'").append(value == null ? "NULL" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(clob.dateValue().getTime()))).append("'");
				} catch (SQLException e) {
					e.printStackTrace();
					sb.append(value == null ? "NULL" : value.toString());
				}

			} else if (value instanceof DATE) {
				DATE clob= (DATE)value;
				sb.append("'").append(value == null ? "NULL" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(clob.dateValue().getTime()))).append("'");

			} else if (value instanceof TIMESTAMPTZ) {
				TIMESTAMPTZ clob= (TIMESTAMPTZ)value;
				try {
					sb.append("'").append(value == null ? "NULL" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(clob.dateValue().getTime()))).append("'");
				} catch (SQLException e) {
					e.printStackTrace();
					sb.append(value == null ? "NULL" : value.toString());
				}

			} else if (value instanceof Clob) {
				Clob clob = (Clob) value;;
				try {
					sb.append("'").append(value == null ? "NULL" :clob.getSubString(1, (int) clob.length())).append("'");
				} catch (Exception e) {
					e.printStackTrace();
				}
			} else if (value instanceof Blob) {
				Blob blob= (Blob)value;
				sb.append(value == null ? "NULL" : value.toString());
			} else if (value instanceof Number) {
				sb.append(value == null ? "NULL" : value.toString());
			} else {
				sb.append("'").append(value == null ? "''" : value.toString().replaceAll("\\r","\\\\r").replaceAll("\\n","\\\\n").replaceAll("'","\\\\'")).append("'");
			}
			sb.append(" ,");
		}
		sb.deleteCharAt(sb.length()-1);

		StringBuffer stringBufferDest = new StringBuffer();
		stringBufferDest.append(" update {0} set {1} where {2} ");
		return MessageFormat.format(stringBufferDest.toString(),bean.getTableName(),sb.toString(),sbId.toString());
	}
}